﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using PMS.ENT;
using System.Data.SqlClient;
using PMS.Utilities;
using System.Configuration;


namespace PMS.DAL
{
    public class FileDAL
    {
        #region Common
        private static FileENT CreateFileFromReader(IDataReader reader)
        {
            FileENT item = new FileENT();
            try
            {

                if (!reader.IsDBNull(reader.GetOrdinal(FileColumns.FileID.ToString()))) item.FileID = Utility.IntParse(reader[FileColumns.FileID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(FileColumns.FileName.ToString()))) item.FileName = Utility.StringParse(reader[FileColumns.FileName.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(FileColumns.ScheduleStatementID.ToString()))) item.ScheduleStatementID = Utility.IntParse(reader[FileColumns.ScheduleStatementID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(FileColumns.FileSize.ToString()))) item.FileSize = Utility.DecimalParse(reader[FileColumns.FileSize.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(FileColumns.FilePath.ToString()))) item.FilePath = Utility.StringParse(reader[FileColumns.FilePath.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(FileColumns.CreatedBy.ToString()))) item.CreatedBy = Utility.IntParse(reader[FileColumns.CreatedBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(FileColumns.UpdateBy.ToString()))) item.UpdateBy = Utility.IntParse(reader[FileColumns.UpdateBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(FileColumns.CreatedDate.ToString()))) item.CreatedDate = Utility.DateTimeParse(reader[FileColumns.CreatedDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(FileColumns.UpdateDate.ToString()))) item.UpdateDate = Utility.DateTimeParse(reader[FileColumns.UpdateDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(FileColumns.Isdeleted.ToString()))) item.Isdeleted = Utility.IntParse(reader[FileColumns.Isdeleted.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(FileColumns.FileMD5.ToString()))) item.FileMD5 = Utility.StringParse(reader[FileColumns.FileMD5.ToString()]);
            }
            catch
            {
                throw;
            }
            return item;
        }
        #endregion

        #region Get
        public static FileENT GetFileByID(int fileID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procFileGetByID.ToString());
                database.AddInParameter(dbCommand, "@FileID", DbType.Int32, fileID);

                FileENT file = new FileENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        file = CreateFileFromReader(reader);
                    }
                    reader.Close();
                }

                return file;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetFileByID");
            }
        }

        public static FileCollection GetFileList(string fileName, int projectID, int scheduleID, int userID, string status, string orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procFileGetList.ToString());
                FileCollection listFile = new FileCollection();
                database.AddInParameter(dbCommand, "@FileName", DbType.String, fileName);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);
                database.AddInParameter(dbCommand, "@ScheduleID", DbType.Int32, scheduleID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@Status", DbType.String, status);
                database.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy);
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection);
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 0);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listFile.Add(CreateFileFromReader(reader));
                    }
                    reader.Close();
                }

                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");

                return listFile;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetFileList");
            }
        }

        public static FileCollection GetFileListByStatementID(int statementID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procFileGetListByStatementID.ToString());
                FileCollection listFile = new FileCollection();
                database.AddInParameter(dbCommand, "@StatementID", DbType.String, statementID);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listFile.Add(CreateFileFromReader(reader));
                    }
                    reader.Close();
                }


                return listFile;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetFileList");
            }
        }

        public static FileCollection GetFileListForCMB(int userID, int projectID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procFileGetListForCMB.ToString());
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);

                FileCollection listFile = new FileCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listFile.Add(CreateFileFromReader(reader));
                    }
                    reader.Close();
                }

                return listFile;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetFileListForCMB");
            }
        }

        #endregion

        #region Insert, Update, Delete

        public static int InsertFile(FileENT file)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procFileInsert.ToString());
                int fileID = 0;
                database.AddOutParameter(dbCommand, "@FileID", DbType.Int32, file.FileID);
                database.AddInParameter(dbCommand, "@FileName", DbType.String, file.FileName);
                database.AddInParameter(dbCommand, "@ScheduleStatementID", DbType.Int32, file.ScheduleStatementID);
                database.AddInParameter(dbCommand, "@FileMD5", DbType.String, file.FileMD5);
                database.AddInParameter(dbCommand, "@FileSize", DbType.Decimal, file.FileSize);
                database.AddInParameter(dbCommand, "@FilePath", DbType.String, file.FilePath);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, file.CreatedBy);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, file.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, file.Isdeleted);
                database.AddInParameter(dbCommand, "@FileAuthor", DbType.String, file.FileAuthor);
                database.AddInParameter(dbCommand, "@FileCategory", DbType.String, file.FileCategory);
                database.AddInParameter(dbCommand, "@FileComment", DbType.String, file.FileComment);
                database.AddInParameter(dbCommand, "@FileCreationDate", DbType.DateTime, file.FileCreationDate);
                database.AddInParameter(dbCommand, "@FileExtension", DbType.String, file.FileExtension);
                database.AddInParameter(dbCommand, "@FileModificationDate", DbType.DateTime, file.FileModificationDate);
                database.AddInParameter(dbCommand, "@FileSubject", DbType.String, file.FileSubject);
                database.AddInParameter(dbCommand, "@FileTitle", DbType.String, file.FileTitle);
                database.AddInParameter(dbCommand, "@FileType", DbType.String, file.FileType);
                database.AddInParameter(dbCommand, "@FileVersion", DbType.Int32, file.FileVersion);

                database.ExecuteNonQuery(dbCommand);
                fileID = Utility.IntParse(database.GetParameterValue(dbCommand, "@FileID"));
                return fileID;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertFile");
            }
        }



        public static int UpdateFile(FileENT file)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procFileUpdate.ToString());

                database.AddInParameter(dbCommand, "@FileID", DbType.Int32, file.FileID);
                database.AddInParameter(dbCommand, "@FileName", DbType.String, file.FileName);
                database.AddInParameter(dbCommand, "@ScheduleStatementID", DbType.Int32, file.ScheduleStatementID);
                database.AddInParameter(dbCommand, "@FileMD5", DbType.String, file.FileMD5);
                database.AddInParameter(dbCommand, "@FileSize", DbType.Decimal, file.FileSize);
                database.AddInParameter(dbCommand, "@FilePath", DbType.String, file.FilePath);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, file.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, file.Isdeleted);

                database.ExecuteNonQuery(dbCommand);

                int fileID = Utility.IntParse(database.GetParameterValue(dbCommand, "@FileID"));
                return fileID;

            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertFile");
            }
        }



        public static int DeleteFile(string fileID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procFileDelete.ToString());
                database.AddInParameter(dbCommand, "@FileID", DbType.String, fileID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertFile");
            }
        }


        public static int ConfirmFile(string fileID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procFileConfirm.ToString());
                database.AddInParameter(dbCommand, "@FileID", DbType.String, fileID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_ConfirmFile");
            }
        }
        #endregion


    }
}
